package com.api.shopapi.mapper;


import com.api.shopapi.entity.Business;
import com.api.shopapi.entity.YearData;
import com.api.shopapi.entity.dto.BusinessDTO;
import com.api.shopapi.entity.dto.BusinessDTO1;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface BusinessMapper {

    @Insert("insert into business (" +
            "BusinessCode," +
            "UserCode," +
            "Address," +
            "AuditStatus," +
            "AuditMessage," +
            "GovernmentCode," +
            "Name," +
            "Nature," +
            "Principal," +
            "Phone," +
            "LegalPerson," +
            "ServiceContent," +
            "ServiceType," +
            "StartTime," +
            "EndTime," +
            "WeekendService," +
            "HolidayService," +
            "FixedPhone," +
            "ServiceRegion," +
            "PublishRegion," +
            "IdPhoto," +
            "BusinessImg," +
            "LicenseCode," +
            "ClosedCircle," +
            "Appendix," +
            "BusinessIntroduce" +
            ") " +
            "values " +
            "(" +
            "#{business.BusinessCode}," +
            "#{business.UserCode}," +
            "#{business.Address}," +
            "#{business.AuditStatus}," +
            "#{business.AuditMessage}," +
            "#{business.GovernmentCode}," +
            "#{business.Name}," +
            "#{business.Nature}," +
            "#{business.Principal}," +
            "#{business.Phone}," +
            "#{business.LegalPerson}," +
            "#{business.ServiceContent}," +
            "#{business.ServiceType}," +
            "#{business.StartTime}," +
            "#{business.EndTime}," +
            "#{business.WeekendService}," +
            "#{business.HolidayService}," +
            "#{business.FixedPhone}," +
            "#{business.ServiceRegion}," +
            "#{business.PublishRegion}," +
            "#{business.IdPhoto}," +
            "#{business.BusinessImg}," +
            "#{business.LicenseCode}," +
            "#{business.ClosedCircle}," +
            "#{business.Appendix}," +
            "#{business.BusinessIntroduce}" +
            ")" )
    int insert(@Param("business") Business business);


    @Delete("delete from business where businessCode=#{businessCode}")
    int deleteByBusinessCode(@Param("businessCode") String businessCode);


    @Update("update business set " +
            "BusinessCode=#{business.BusinessCode}," +
            "UserCode=#{business.UserCode}," +
            "Address=#{business.Address}," +
            "AuditStatus=#{business.AuditStatus}," +
            "AuditMessage=#{business.AuditMessage}," +
            "GovernmentCode=#{business.GovernmentCode}," +
            "Name=#{business.Name}," +
            "Nature=#{business.Nature}," +
            "Principal=#{business.Principal}," +
            "Phone=#{business.Phone}," +
            "LegalPerson=#{business.LegalPerson}," +
            "ServiceContent=#{business.ServiceContent}," +
            "ServiceType=#{business.ServiceType}," +
            "StartTime=#{business.StartTime}," +
            "EndTime=#{business.EndTime}," +
            "WeekendService=#{business.WeekendService}," +
            "HolidayService=#{business.HolidayService}," +
            "FixedPhone=#{business.FixedPhone}," +
            "ServiceRegion=#{business.ServiceRegion}," +
            "PublishRegion=#{business.PublishRegion}," +
            "IdPhoto=#{business.IdPhoto}," +
            "BusinessImg=#{business.BusinessImg}," +
            "LicenseCode=#{business.LicenseCode}," +
            "ClosedCircle=#{business.ClosedCircle}," +
            "Appendix=#{business.Appendix}," +
            "BusinessIntroduce=#{business.BusinessIntroduce}" +
            " where BusinessCode=#{business.BusinessCode}")
    int updateByBusinessCode(@Param("business") Business business);


    @Select("select * from business where businessCode=#{businessCode} limit 1")
    Business getByBusinessCode(@Param("businessCode") String businessCode);

    @Select("select * from business where userCode=#{userCode} limit 1")
    Business getByUserCode(@Param("userCode") String userCode);


    @Select("select * from business")
    List<Business> getList();

    @Select("select * from business")
    List<BusinessDTO> getListBusinessDTO();

    @Select("select * from business where governmentCode=#{governmentCode}")
    List<BusinessDTO> getListBusinessDTOByGovernmentCode(String governmentCode);

    @Select("select SUM(ob.Price) as TotalPrice,SUM(case when ob.OrderType=2 then ob.Price else 0 end) as ServeOrderPrice," +
            "SUM(case when ob.OrderType=1 then ob.Price else 0 end) as CommonOrderPrice ," +
            "COUNT(ob.OrderBusinessCode) as TotalCount, COUNT(CASE WHEN ob.OrderType = 2 THEN 1 ELSE NULL END) AS ServeOrderCount," +
            "COUNT(CASE WHEN ob.OrderType = 1 THEN 1 ELSE NULL END) AS CommonOrderCount,b.* " +
            "from business b left join order_business ob on ob.BusinessCode=b.BusinessCode  " +
            "where ob.BusinessCode=#{businessCode} " +
            "and ob.CreateTime between #{startTime} and #{endTime} " +
            "and ob.Status in (3,7,14,16) " +
            "group BY ob.BusinessCode")
    BusinessDTO1 businessOrderStatistics(@Param("businessCode")String businessCode, @Param("startTime")String startTime,
                                               @Param("endTime")String endTime);

    @Select("select * from business where governmentCode=#{governmentCode}")
    List<BusinessDTO1> getPageForGov(@Param("governmentCode") String governmentCode);


    @Select("select IFNULL(SUM(case MONTH(CreateTime) when 1 then Price else 0 end),0) as JanuaryPrice ," +
            "count(case MONTH(CreateTime) when 1 then 1 else null end) as JanuaryCount," +
            "IFNULL(SUM(case MONTH(CreateTime) when 2 then Price else 0 end),0) as FebruaryPrice ," +
            "count(case MONTH(CreateTime) when 2 then 1 else null end) as FebruaryCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 3 then Price else 0 end),0) as MarchPrice ," +
            "count(case MONTH(CreateTime) when 3 then 1 else null end) as MarchCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 4 then Price else 0 end),0) as AprilPrice ," +
            "count(case MONTH(CreateTime) when 4 then 1 else null end) as AprilCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 5 then Price else 0 end),0) as MayPrice ," +
            "count(case MONTH(CreateTime) when 5 then 1 else null end) as MayCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 6 then Price else 0 end),0) as JunePrice ," +
            "count(case MONTH(CreateTime) when 6 then 1 else null end) as JuneCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 7 then Price else 0 end),0) as JulyPrice ," +
            "count(case MONTH(CreateTime) when 7 then 1 else null end) as JulyCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 8 then Price else 0 end),0) as AugustPrice ," +
            "count(case MONTH(CreateTime) when 8 then 1 else null end) as AugustCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 9 then Price else 0 end),0) as SeptemberPrice ," +
            "count(case MONTH(CreateTime) when 9 then 1 else null end) as SeptemberCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 10 then Price else 0 end),0) as OctoberPrice ," +
            "count(case MONTH(CreateTime) when 10 then 1 else null end) as OctoberCount ," +
            "IFNULL(SUM(case MONTH(CreateTime) when 11 then Price else 0 end),0) as NovemberPrice ," +
            "count(case MONTH(CreateTime) when 11 then 1 else null end) as NovemberCount," +
            "IFNULL(SUM(case MONTH(CreateTime) when 12 then Price else 0 end),0) as DecemberPrice ," +
            "count(case MONTH(CreateTime) when 12 then 1 else null end) as DecemberCount  " +
            "from order_business where YEAR(CreateTime) = YEAR(NOW()) and BusinessCode=#{businessCode} " +
            "and Status in (3,7,14,16)")
    YearData getBusinessYearData(@Param("businessCode")String businessCode);




}
